-- Copyright (c) Microsoft Corporation. All rights reserved.

IF OBJECT_ID ( 'prc_iiUpdateActionState', 'P' ) IS NOT NULL 
    DROP PROCEDURE prc_iiUpdateActionState;
GO

PRINT 'Creating Procedure prc_iiUpdateActionState'
GO

CREATE PROCEDURE prc_iiUpdateActionState
	 @ActionId      int,
	 @State         int
AS

DECLARE @errorstatus         INT

-- Initialize the ProcedureName for error messages.
DECLARE @procedureName SYSNAME
SELECT  @procedureName = @@SERVERNAME + '.' + db_name() + '..' + object_name(@@PROCID)

IF (@@TRANCOUNT = 0)
BEGIN
    RAISERROR (500002, 16, -1, @procedureName)
    RETURN 500002
END

UPDATE  [ActionData]
SET State=@State
WHERE ActionId=@ActionId
	
SET @errorstatus = @@ERROR
IF (@errorstatus <> 0)
BEGIN
    RAISERROR (500004, 16, -1, @procedureName, @errorstatus, N'UPDATE', N'ActionData')
    RETURN 500004
END
GO

GRANT EXECUTE ON prc_iiUpdateActionState TO UPSDEXEC
GO


/***********************************************************************************/
/***********************************************************************************/

IF OBJECT_ID ( 'prc_iiCreateActionData', 'P' ) IS NOT NULL 
    DROP PROCEDURE prc_iiCreateActionData;
GO

PRINT 'Creating Procedure prc_iiCreateActionData'
GO

CREATE PROCEDURE prc_iiCreateActionData
	 @ChangeGroupID            int,
	 @SourceItem               xml,
	 @TargetSourceItem         xml,
	 @TargetTargetItem         xml,
	 @BasicAction              int,
	 @Recursivity              bit,
	 @Order                    int,
	 @State                    int,
	 @Label					   nvarchar(max),
	 @Version				   nvarchar(max),
	 @MergeVersionTo           nvarchar(max)
AS

DECLARE @errorstatus         INT

-- Initialize the ProcedureName for error messages.
DECLARE @procedureName SYSNAME
SELECT  @procedureName = @@SERVERNAME + '.' + db_name() + '..' + object_name(@@PROCID)

IF (@@TRANCOUNT = 0)
BEGIN
    RAISERROR (500002, 16, -1, @procedureName)
    RETURN 500002
END

INSERT 
INTO [ActionData]
	(
	 ChangeGroupID,
	 [Order],
	 State,
	 SourceItem,
	 TargetSourceItem,
	 TargetTargetItem,
	 BasicAction,
	 Recursivity,
	 Label,
	 Version,
	 MergeVersionTo
	)
     VALUES
	(	 
	 @ChangeGroupID,
	 @Order,
	 @State,
	 @SourceItem,
	 @TargetSourceItem,
	 @TargetTargetItem,
	 @BasicAction,
	 @Recursivity,
	 @Label,
	 @Version,
	 @MergeVersionTo
	)
	
SELECT @@IDENTITY

SET @errorstatus = @@ERROR
IF (@errorstatus <> 0)
BEGIN
    RAISERROR (500004, 16, -1, @procedureName, @errorstatus, N'INSERT', N'ActionData')
    RETURN 500004
END
GO

GRANT EXECUTE ON prc_iiCreateActionData TO UPSDEXEC
GO

/***********************************************************************************/
/***********************************************************************************/


IF OBJECT_ID ( 'prc_iiCreateChangeGrouping', 'P' ) IS NOT NULL 
    DROP PROCEDURE prc_iiCreateChangeGrouping;
GO

PRINT 'Creating Procedure prc_iiCreateChangeGrouping'
GO

CREATE PROCEDURE prc_iiCreateChangeGrouping
	@ExecutionOrder	[bigint],
	@Owner			[nvarchar](max),
	@Comment		[nvarchar](2048),
	@ChangeTime		[datetime],
	@Status			[int],
	@SessionId		[nvarchar](max),
	@Direction      [int],
	@Name			[nvarchar](max)

AS

DECLARE @errorstatus         INT

-- Initialize the ProcedureName for error messages.
DECLARE @procedureName SYSNAME
SELECT  @procedureName = @@SERVERNAME + '.' + db_name() + '..' + object_name(@@PROCID)

IF (@@TRANCOUNT = 0)
BEGIN
    RAISERROR (500002, 16, -1, @procedureName)
    RETURN 500002
END

IF (@ExecutionOrder = 0)
BEGIN
	SET @ExecutionOrder = (SELECT TOP 1 ExecutionOrder+1 FROM ChangeGroups ORDER BY ExecutionOrder DESC)
	IF @ExecutionOrder IS NULL
	BEGIN
		SET @ExecutionOrder = 1
	END
END

INSERT 
INTO [ChangeGroups]
	([ExecutionOrder],
	 [Owner],
	 [Comment],
	 [ChangeTime],
	 [Status],
	 [SessionId],
	 [Direction],
	 [Name])
     VALUES
	(@ExecutionOrder,
	 @Owner,
	 @Comment,
	 @ChangeTime,
	 @Status,
	 @SessionId,
	 @Direction,
	 @Name)

SET @errorstatus = @@ERROR
IF (@errorstatus <> 0)
BEGIN
    RAISERROR (500004, 16, -1, @procedureName, @errorstatus, N'INSERT', N'ChangeGroups')
    RETURN 500004
END

SELECT * FROM ChangeGroups where ChangeGroupID=@@IDENTITY
GO

GRANT EXECUTE ON prc_iiCreateChangeGrouping TO UPSDEXEC
GO

/***********************************************************************************/
/***********************************************************************************/

IF OBJECT_ID ( 'prc_iiDemoteInProgressActionsToPending', 'P' ) IS NOT NULL 
    DROP PROCEDURE prc_iiDemoteInProgressActionsToPending;
GO

PRINT 'Creating Procedure prc_iiDemoteInProgressActionsToPending'
GO

CREATE PROCEDURE prc_iiDemoteInProgressActionsToPending
	@SessionId nvarchar(max),
	@Direction int
AS

DECLARE @errorstatus         INT

-- Initialize the ProcedureName for error messages.
DECLARE @procedureName SYSNAME
SELECT  @procedureName = @@SERVERNAME + '.' + db_name() + '..' + object_name(@@PROCID)

IF (@@TRANCOUNT = 0)
BEGIN
    RAISERROR (500002, 16, -1, @procedureName)
    RETURN 500002
END

UPDATE [ChangeGroups] 
WITH (UPDLOCK)
SET [Status]=1  -- pending 
WHERE [Status]=2  -- in progress
	AND SessionId=@SessionId
	AND Direction=@Direction

SET @errorstatus = @@ERROR
IF (@errorstatus <> 0)
BEGIN
    RAISERROR (500004, 16, -1, @procedureName, @errorstatus, N'UPDATE', N'ChangeGroups')
    RETURN 500004
END
GO

GRANT EXECUTE ON prc_iiDemoteInProgressActionsToPending TO UPSDEXEC
GO

/***********************************************************************************/
/***********************************************************************************/


IF OBJECT_ID ( 'prc_iiPromoteAnalysisToPending', 'P' ) IS NOT NULL 
    DROP PROCEDURE prc_iiPromoteAnalysisToPending;
GO

PRINT 'Creating Procedure prc_iiPromoteAnalysisToPending'
GO

CREATE PROCEDURE prc_iiPromoteAnalysisToPending
	@SessionId	[nvarchar](max),
	@Direction  int
AS

DECLARE @errorstatus         INT

-- Initialize the ProcedureName for error messages.
DECLARE @procedureName SYSNAME
SELECT  @procedureName = @@SERVERNAME + '.' + db_name() + '..' + object_name(@@PROCID)

IF (@@TRANCOUNT = 0)
BEGIN
    RAISERROR (500002, 16, -1, @procedureName)
    RETURN 500002
END

UPDATE [ChangeGroups]
WITH (UPDLOCK)
SET [Status]=1 -- pending
WHERE [SessionId] = @SessionId
	  AND [Status]=0 -- analysis
	  AND Direction=@Direction

SET @errorstatus = @@ERROR
IF (@errorstatus <> 0)
BEGIN
    RAISERROR (500004, 16, -1, @procedureName, @errorstatus, N'UPDATE', N'ChangeGroups')
    RETURN 500004
END
GO

GRANT EXECUTE ON prc_iiPromoteAnalysisToPending TO UPSDEXEC
GO

/***********************************************************************************/
/***********************************************************************************/


IF OBJECT_ID ( 'prc_iiUpdateChangeGroupingStatus', 'P' ) IS NOT NULL 
    DROP PROCEDURE prc_iiUpdateChangeGroupingStatus;
GO

PRINT 'Creating Procedure prc_iiUpdateChangeGroupingStatus'
GO
CREATE PROCEDURE prc_iiUpdateChangeGroupingStatus
	@ChangeGroupID [int],
	@Status [int]
AS

DECLARE @errorstatus INT

-- Initialize the ProcedureName for error messages.
DECLARE @procedureName SYSNAME
SELECT  @procedureName = @@SERVERNAME + '.' + db_name() + '..' + object_name(@@PROCID)

IF (@@TRANCOUNT = 0)
BEGIN
    RAISERROR (500002, 16, -1, @procedureName)
    RETURN 500002
END

UPDATE ChangeGroups
SET 
	Status = @Status
WHERE
	ChangeGroupID = @ChangeGroupID

SET @errorstatus = @@ERROR
IF (@errorstatus <> 0)
BEGIN
    RAISERROR (500004, 16, -1, @procedureName, @errorstatus, N'UPDATE', N'ChangeGroups')
    RETURN 500004
END
GO

GRANT EXECUTE ON prc_iiUpdateChangeGroupingStatus TO UPSDEXEC
GO

/***********************************************************************************/
/***********************************************************************************/


IF OBJECT_ID ( 'prc_QueryNextChangeGrouping', 'P' ) IS NOT NULL 
    DROP PROCEDURE prc_QueryNextChangeGrouping;
GO

PRINT 'Creating Procedure prc_QueryNextChangeGrouping'
GO

CREATE PROCEDURE prc_QueryNextChangeGrouping
	@SessionId nvarchar(max),
	@Direction int
AS

SELECT TOP 1 * FROM ChangeGroups
WHERE
	SessionId = @SessionId AND
	Direction = @Direction AND
	Status = 1		-- Pending
ORDER BY ExecutionOrder ASC

GRANT EXECUTE ON prc_QueryNextChangeGrouping TO UPSDEXEC
GO

/***********************************************************************************/
/***********************************************************************************/


IF OBJECT_ID ( 'prc_QueryActionData', 'P' ) IS NOT NULL 
    DROP PROCEDURE prc_QueryActionData;
GO

PRINT 'Creating Procedure prc_QueryActionData'
GO

CREATE PROCEDURE prc_QueryActionData
	@ChangeGroupID int
AS

SELECT * FROM ActionData
WHERE    ChangeGroupID = @ChangeGroupID
ORDER BY [Order] ASC
GO

GRANT EXECUTE ON prc_QueryActionData TO UPSDEXEC
GO

/***********************************************************************************/
/***********************************************************************************/

/* Store and Load session state */

IF OBJECT_ID ( 'prc_LoadSessionVariable', 'P' ) IS NOT NULL 
    DROP PROCEDURE prc_LoadSessionVariable;
GO

PRINT 'Creating Procedure prc_LoadSessionVariable'
GO

CREATE PROCEDURE prc_LoadSessionVariable
	@SessionId nvarchar(max),
	@Variable nvarchar(256)
AS

SELECT TOP 1 Value FROM SessionState
WHERE	SessionId=@SessionId
		AND Variable=@Variable
GO

GRANT EXECUTE ON prc_LoadSessionVariable TO UPSDEXEC
GO

/***********************************************************************************/
/***********************************************************************************/


IF OBJECT_ID ( 'prc_UpdateSessionVariable', 'P' ) IS NOT NULL 
    DROP PROCEDURE prc_UpdateSessionVariable;
GO

PRINT 'Creating Procedure prc_UpdateSessionVariable'
GO


CREATE PROCEDURE prc_UpdateSessionVariable
	@SessionId nvarchar(max),
	@Variable nvarchar(256),
	@Value nvarchar(max)
AS

IF EXISTS(
	SELECT TOP 1 * FROM SessionState
	WHERE	SessionId=@SessionId
			AND Variable=@Variable
	)
	BEGIN
		UPDATE SessionState
		SET Value=@Value
		WHERE	SessionId=@SessionId
				AND Variable=@Variable 
	END
	ELSE
	BEGIN
		INSERT INTO SessionState VALUES(@SessionId, @Variable, @Value)
	END
GO

GRANT EXECUTE ON prc_UpdateSessionVariable TO UPSDEXEC
GO


/***********************************************************************************/
/***********************************************************************************/


IF OBJECT_ID ( 'prc_AddConversionHistory', 'P' ) IS NOT NULL 
    DROP PROCEDURE prc_AddConversionHistory;
GO

PRINT 'Creating Procedure prc_AddConversionHistory'
GO

CREATE PROCEDURE prc_AddConversionHistory
	@SessionId [nvarchar](max),
	@SourceChangeId [nvarchar](max),
	@TfsChangesetId [int],
	@UtcWhen [datetime],
	@FromSource [bit],
	@Comment [nvarchar](max)
AS

	INSERT INTO ConversionHistory
	(
		[SessionId],
		[SourceChangeId],
		[TfsChangesetId],
		[UtcWhen],
		[FromSource],
		[Comment]
	)
	VALUES
	(
		@SessionId,
		@SourceChangeId,
		@TfsChangesetId,
		@UtcWhen,
		@FromSource,
		@Comment
	)
GO
	
GRANT EXECUTE ON prc_AddConversionHistory TO UPSDEXEC
GO

PRINT 'Creating Procedure prc_QueryConversionHistory'
GO

CREATE PROCEDURE prc_QueryConversionHistory
	@SessionId [nvarchar](max),
	@SourceChangeId [nvarchar](max),
	@TfsChangesetId [int],
	@FromSource [bit]
AS
IF (@FromSource <> 0)
	SELECT [SourceChangeId], [TfsChangesetId], [UtcWhen], [Comment] FROM ConversionHistory
	WHERE [SessionId]=@SessionId AND
		  [SourceChangeId]=@SourceChangeId 
ELSE
	SELECT [SourceChangeId], [TfsChangesetId], [UtcWhen], [Comment] FROM ConversionHistory
	WHERE [SessionId]=@SessionId AND
		  [TfsChangesetId]=@TfsChangesetId 
		  
GO

GRANT EXECUTE ON prc_QueryConversionHistory TO UPSDEXEC
GO
